package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.StringTokenizer;

import javax.swing.tree.TreePath;

public class AddDbFunctions 
{
	public static void addUser(String name, String surname, String login)
	{
		Connection con;
		PreparedStatement ps;
		ResultSet rs;
		String statement;
		Integer max_id;
		
		try
		{
			con = Connector.getConnection();
			
			statement = "SELECT MAX_ID_VAL FROM MAX_ID WHERE TABLE_NAME = 'USER'";
			ps = con.prepareStatement(statement);
			
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				max_id = rs.getInt("MAX_ID_VAL");
			}
			else
				return;
			
			//continue only id MAX_ID was found
			
			statement = "INSERT INTO USER VALUES (" + max_id + ", '" + name + "', '" + surname + "', '" + login + "')";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			statement = "UPDATE MAX_ID SET MAX_ID_VAL = " + (max_id + 1) + " WHERE TABLE_NAME = 'USER'";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			ps.close();
			con.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void addAccount(String account_number, Integer bank_number, String desc)
	{
		Connection con;
		PreparedStatement ps;
		ResultSet rs;
		String statement;
		Integer max_id;
		Integer user_id = 1;
		
		try
		{
			con = Connector.getConnection();
			
			statement = "SELECT MAX_ID_VAL FROM MAX_ID WHERE TABLE_NAME = 'ACCOUNT'";
			ps = con.prepareStatement(statement);
			
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				max_id = rs.getInt("MAX_ID_VAL");
			}
			else
				return;
			
			//continue only id MAX_ID was found
			
			statement = "INSERT INTO ACCOUNT VALUES (" + max_id + ", " + user_id + ", '" + account_number + "', " + bank_number + ", '" + desc + "')";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			statement = "UPDATE MAX_ID SET MAX_ID_VAL = " + (max_id + 1) + " WHERE TABLE_NAME = 'ACCOUNT'";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			ps.close();
			con.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void addCategory(String name, String desc, String parent)
	{
		Connection con;
		PreparedStatement ps;
		ResultSet rs;
		String statement;
		String treeStructBuilder;
		Integer max_id;
		Integer parent_id;
		
		try
		{
			if(name.isEmpty())
				return;
			
			con = Connector.getConnection();
			
			statement = "SELECT ID FROM CATEGORY WHERE NAME = '" + name + "'";
			ps = con.prepareStatement(statement);
			rs = ps.executeQuery();
			if(rs.next())
				return;
			
			statement = "SELECT MAX_ID_VAL FROM MAX_ID WHERE TABLE_NAME = 'CATEGORY'";
			ps = con.prepareStatement(statement);
			
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				max_id = rs.getInt("MAX_ID_VAL");
			}
			else
				return;
			rs.close();
			ps.close();
			//continue only id MAX_ID was found
			
			parent = parent.replace("[", "").replace("]", "");
			if(parent.contains(","))
				parent = parent.substring(parent.lastIndexOf(",")+2).trim();
			
			if(parent!=null && !parent.equalsIgnoreCase("Root"))
			{	statement = "SELECT ID FROM CATEGORY WHERE name = '" + parent + "'";
				ps = con.prepareStatement(statement);
				
				rs = ps.executeQuery();
				
				if(rs.next())
				{
					parent_id = rs.getInt("ID");
				}
				else
					return;
				
				rs.close();
				ps.close();
			}
			else
				parent_id = 0;
			
			statement = "SELECT max(treeStructBuilder) FROM CATEGORY WHERE PARENT_ID = " + parent_id;
			ps = con.prepareStatement(statement);
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				treeStructBuilder = rs.getString(1);
				rs.close();
				ps.close();
				if(treeStructBuilder!=null)
				{	
					int structSequence = new Integer(treeStructBuilder.substring(treeStructBuilder.lastIndexOf(".")+1));
					structSequence++;
					treeStructBuilder = treeStructBuilder.substring(0, treeStructBuilder.lastIndexOf(".")+1) + (structSequence < 10 ? "0"+structSequence : structSequence);
				}
				else
				{
					statement = "SELECT treeStructBuilder FROM CATEGORY WHERE ID = " + parent_id;
					ps = con.prepareStatement(statement);
					rs = ps.executeQuery();
					
					if(rs.next())
					{
						treeStructBuilder = rs.getString(1) + ".01";
					}
					else 
						treeStructBuilder = "01";
					
					rs.close();
					ps.close();
				}
			}
			else
			{
				rs.close();
				ps.close();
				return;
			}
			
			statement = "INSERT INTO CATEGORY VALUES (" + max_id + ", " + parent_id + ", '" + name + "', '" + desc + "', '" + treeStructBuilder +"')";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			statement = "UPDATE MAX_ID SET MAX_ID_VAL = " + (max_id + 1) + " WHERE TABLE_NAME = 'CATEGORY'";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			ps.close();
			con.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
//	public static void addSubcategory(String name, String desc, String category_reference)
//	{
//		Connection con;
//		PreparedStatement ps;
//		ResultSet rs;
//		String statement;
//		Integer max_id;
//		Integer category_id;
//		
//		try
//		{
//			con = Connector.getConnection();
//			
//			statement = "SELECT MAX_ID_VAL FROM MAX_ID WHERE TABLE_NAME = 'SUBCATEGORY'";
//			ps = con.prepareStatement(statement);
//			
//			rs = ps.executeQuery();
//			
//			if(rs.next())
//			{
//				max_id = rs.getInt("MAX_ID_VAL");
//			}
//			else
//				return;
//			
//			//continue only id MAX_ID was found
//			
//			statement = "SELECT ID FROM CATEGORY WHERE NAME = '" + category_reference + "'";
//			ps = con.prepareStatement(statement);
//			
//			rs = ps.executeQuery();
//			
//			if(rs.next())
//			{
//				category_id = rs.getInt("ID");
//			}
//			else
//				return;
//			
//			statement = "INSERT INTO SUBCATEGORY VALUES (" + max_id + ", " + category_id + ", '" + name + "', '" + desc + "')";
//			ps = con.prepareStatement(statement);
//			ps.executeUpdate();
//			
//			statement = "UPDATE MAX_ID SET MAX_ID_VAL = " + (max_id + 1) + " WHERE TABLE_NAME = 'SUBCATEGORY'";
//			ps = con.prepareStatement(statement);
//			ps.executeUpdate();
//			
//			ps.close();
//			con.close();
//		}
//		catch (SQLException e)
//		{
//			e.printStackTrace();
//		}
//	}
	
	public static void addExpenses(String account, TreePath[] categories, Date date, String price, String description, Integer drOdCr)
	{
		Connection con;
		PreparedStatement ps;
		ResultSet resSet;
		int rs;
		Integer max_id;
		String statement;
		String accnNumber;
		String bankNumber;
		
		if(	account == null || account.isEmpty() || 
			categories == null || categories.length==0 || 
			price == null || price.isEmpty())
				return;
		
		StringTokenizer st = new StringTokenizer(account, "/");
		if(st.countTokens()==2)
		{
			accnNumber = st.nextToken();
			bankNumber = st.nextToken();
		}
		else
		{
			accnNumber = st.nextToken();
			bankNumber = null;
		}
		
		try
		{
			con = Connector.getConnection();
			
			statement = "SELECT MAX_ID_VAL FROM MAX_ID WHERE TABLE_NAME = 'EXPENSES'";
			ps = con.prepareStatement(statement);
			
			resSet = ps.executeQuery();
			
			if(resSet.next())
			{
				max_id = resSet.getInt("MAX_ID_VAL");
			}
			else
				return;
			
			//continue only id MAX_ID was found
			
			statement = "insert into EXPENSES (ID,USER_ID,ACCOUNT_ID,VALUE,DATE,DEBET_OR_CREDIT,DESCRIPTION) "+
						"SELECT"+
						"    "+max_id+" as id, "+
						"    1 as user_id, "+
						"    acc.id as account_id, "+
						"    "+price+" as value, "+
						"    '" + date + "', " +
						"    "+drOdCr+" as debet_or_credit, "+ 
						"    '"+description+"' as description "+
						"from ACCOUNT as acc where acc.ACCOUNT_NUMBER = '" +accnNumber + "' "+ ((bankNumber != null) ? (" AND acc.BANK_NUMBER = "+bankNumber) : "");
			
			ps = con.prepareStatement(statement);
			
			rs = ps.executeUpdate();

			ps.close();
			
			if(rs!=1)
			{
				System.out.println("Zly zapis do tabulky");
				return;
			}
			
			statement = "UPDATE MAX_ID SET MAX_ID_VAL = " + (max_id + 1) + " WHERE TABLE_NAME = 'EXPENSES'";
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			
			ps.close();
			
			String wherePart = "";
			
			for (int i = 0; i < categories.length; i++) 
			{
				wherePart += "NAME = '" + categories[i].getLastPathComponent().toString() + "' OR ";
			}
			
			wherePart = wherePart.substring(0, wherePart.lastIndexOf("OR"));
			
			statement = "INSERT INTO EXPENSE_CATEGORY " +
					"SELECT " +
					max_id + ", " +
					"ID " +
					"FROM CATEGORY WHERE " + wherePart;
			
			ps = con.prepareStatement(statement);
			ps.executeUpdate();
			ps.close();
			
			con.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}
